import math
import time
from datetime import datetime, timedelta
from typing import Dict, Any, List, Tuple

import pandas as pd
import requests


def fetch_api_data(api_config: Dict[str, Any], page_no: int, page_size: int, region_index_code: str) -> Tuple[
    List[Dict], int]:
    """
    分页获取API数据，包含错误处理

    Args:
        api_config: API配置字典，包含url、headers和payload模板
        page_no: 页码
        page_size: 每页大小
        region_index_code: 区域索引代码

    Returns:
        tuple: (数据列表, 总记录数)
    """
    try:
        # 复制payload并添加分页参数和具体的regionIndexCode
        payload = api_config['payload'].copy()
        payload["pageNo"] = page_no
        payload["pageSize"] = page_size
        payload["regionIndexCode"] = region_index_code

        response = requests.post(
            api_config['url'],
            headers=api_config['headers'],
            json=payload
        )
        response.raise_for_status()  # 检查HTTP错误
        json_data = response.json()

        if json_data.get('code') != 0:
            raise ValueError(f"API返回错误码: {json_data.get('code')}, 消息: {json_data.get('message')}")

        data = json_data.get('data', {}).get('data', {})
        return data.get('list', []), data.get('total', 0)

    except requests.exceptions.RequestException as e:
        print(f"请求失败 (regionIndexCode: {region_index_code}): {e}")
        return [], 0
    except ValueError as ve:
        print(ve)
        return [], 0


def process_date_fields(data_list: List[Dict]) -> List[Dict]:
    """处理API返回的日期字段（如updateTime/createTime）"""
    for item in data_list:
        date_fields = ['updateTime', 'createTime']
        for field in date_fields:
            if field in item:
                timestamp = item[field]
                # 处理毫秒级时间戳
                if isinstance(timestamp, (int, float)) and timestamp > 0:
                    try:
                        dt = datetime.utcfromtimestamp(timestamp / 1000)
                        item[field] = dt.strftime('%Y-%m-%d')
                    except (ValueError, OSError):
                        item[field] = None
                # 校验字符串日期格式
                elif isinstance(timestamp, str):
                    try:
                        datetime.strptime(timestamp, '%Y-%m-%d')
                    except ValueError:
                        item[field] = None
    return data_list


def add_sync_date(data_list: List[Dict], sync_date: str) -> List[Dict]:
    """为每条数据添加同步日期字段"""
    for item in data_list:
        item['sync_date'] = sync_date
    return data_list


def add_region_index_code(data_list: List[Dict], region_index_code: str) -> List[Dict]:
    """为每条数据添加region_index_code字段，标识数据来源"""
    for item in data_list:
        item['region_index_code'] = region_index_code
    return data_list


def save_to_excel(data_list: List[Dict], filename_prefix: str = "api_data") -> None:
    """保存数据到Excel并确保日期格式正确，同步日期字段放在首列"""
    if not data_list:
        print("无有效数据可保存")
        return

    try:
        df = pd.DataFrame(data_list)

        # 重新排列列顺序，将sync_date和region_index_code放在前面
        priority_columns = []
        if 'sync_date' in df.columns:
            priority_columns.append('sync_date')
        if 'region_index_code' in df.columns:
            priority_columns.append('region_index_code')

        other_columns = [col for col in df.columns if col not in priority_columns]
        columns = priority_columns + other_columns
        df = df[columns]

        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"{filename_prefix}_{timestamp}.xlsx"

        # 使用ExcelWriter设置日期格式
        with pd.ExcelWriter(filename, engine='openpyxl', datetime_format='yyyy-mm-dd') as writer:
            df.to_excel(writer, index=False)
        print(f"数据已保存至 {filename}，共 {len(df)} 条记录")

    except Exception as e:
        print(f"保存Excel失败: {e}")


def fetch_data_for_region(api_config: Dict[str, Any], region_index_code: str, sync_date: str) -> List[Dict]:
    """获取单个regionIndexCode的所有数据"""
    print(f"\n开始获取 regionIndexCode: {region_index_code} 的数据")

    # 初始请求获取总记录数
    _, total_records = fetch_api_data(api_config, 1, 1000, region_index_code)
    if total_records == 0:
        print(f"regionIndexCode {region_index_code} 未获取到有效数据")
        return []

    print(f"总记录数: {total_records}")
    page_size = 100  # 每页大小
    total_pages = math.ceil(total_records / page_size)
    all_data = []

    # 分页获取数据
    for page in range(1, total_pages + 1):
        page_data, _ = fetch_api_data(api_config, page, page_size, region_index_code)
        if page_data:
            processed_data = process_date_fields(page_data)  # 处理原始日期字段
            processed_data = add_sync_date(processed_data, sync_date)  # 添加同步日期字段
            processed_data = add_region_index_code(processed_data, region_index_code)  # 添加区域标识
            all_data.extend(processed_data)
            print(f"已获取第 {page}/{total_pages} 页 | 本页 {len(page_data)} 条")
        time.sleep(0.5)  # 避免请求过频

    print(f"完成获取 regionIndexCode: {region_index_code}，共 {len(all_data)} 条数据")
    return all_data


def main():
    """主函数，配置API参数并执行数据获取流程"""
    # 计算前一天日期（格式: YYYY-MM-DD）
    sync_date = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')
    print(f"同步日期字段值: {sync_date}")

    # API配置参数
    api_config = {
        'url': 'http://10.135.13.2:48082/admin-api/infra/videoplatform/camerasRegionIndexCode',
        'headers': {
            'User-Agent': 'Apifox/1.0.0 (https://apifox.com)',
            'Content-Type': 'application/json'
        },
        'payload': {
            "treeCode": "0",
            "env": 1
            # regionIndexCode 将在每次请求时动态添加
        }
    }

    # 定义多个regionIndexCode值
    region_index_codes = [
        "52010267002160030105",
        "52010267002160030080",
        "52010267002160020014",
        "52010367002160000215",
        "52010367002160000061",
        "52010367002160000111",
        "52010367002160000049",
        "52010167002160000010",
        "52012267002160000072",
        "52012267002160000102",
        "52010267002160030025",
        "52010267002160030155",
        "52012267002160000043",
        "52010367002160000169",
        "52010367002160000183",
        "52010367002160000176",
        "52010367002160000224",
        "52010367002160000213",
        "52010367002160000117",
        "52010367002160000031",
        "52010167002160000009",
        "52010267002160030158",
        "52010267002160100050",
        "52010267002160030061",
        "52010267002160030056",
        "52010367002160000023",
        "52010367002160000080",
        "52010367002160000236",
        "52012267002160000168",
        "52010367002160000260",
        "52012267002160000109",
        "52012267002160000079",
        "52010267002160100045",
        "52010267002160030104",
        "52010267002160030074",
        "52012267002160000044",
        "52010367002160000048",
        "52010367002160000280",
        "52010367002160000171",
        "52010367002160000275",
        "52012267002160000012",
        "52010167002160000011",
        "52010167002160000039",
        "52011167002160000001",
        "52010267002160030146",
        "52010267002160030110",
        "52010267002160030057",
        "52010267002160100018",
        "52010267002160030095",
        "52010267002160030060",
        "52012267002160000169",
        "52010367002160000200",
        "52010367002160000104",
        "52010367002160000043",
        "52012267002160000120",
        "52012267002160000108",
        "52010267002160000001",
        "52010267002160030107",
        "52010267002160030082",
        "52010267002160030021",
        "52010267002160030016",
        "52012267002160000026",
        "52010267002160020009",
        "52012267002160000039",
        "52012267002160000047",
        "52010267002160100059",
        "52010367002160000187",
        "52010367002160000054",
        "52010367002160000035",
        "52010367002160000198",
        "52010367002160000096",
        "52010367002160000124",
        "52010167002160000025",
        "52012267002160000156",
        "52010267002160030144",
        "52010267002160030034",
        "52010267002160030088",
        "52012267002160000033",
        "52010367002160000076",
        "52010367002160000008",
        "52010367002160000017",
        "52010367002160000020",
        "52012267002160000115",
        "52010367002160000150",
        "52010167002160000018",
        "52010267002160030031",
        "52010267002160030169",
        "52010267002160100056",
        "52010267002160030092",
        "52010267002160030050",
        "52010267002160030183",
        "52010267002160030006",
        "52012267002160000127",
        "52012267002160000029",
        "52012267002160000138",
        "52012267002160000036",
        "52010367002160000086",
        "52010367002160000230",
        "52012267002160000110",
        "52010367002160000266",
        "52010367002160000012",
        "52012267002160000048",
        "52010367002160000218",
        "52010367002160000251",
        "52010267002160030103",
        "52010267002160030012",
        "52010267002160030073",
        "52010267002160030044",
        "52010267002160030086",
        "52010267002160100023",
        "52010267002160030099",
        "52012267002160000165",
        "52010267002160020012",
        "52010367002160000287",
        "52010367002160000078",
        "52010367002160000092",
        "52012267002160000022",
        "52010367002160000050",
        "52010167002160000016",
        "52010167002160000021",
        "52010367002160000302",
        "52010267002160030133",
        "52010267002160030165",
        "52010267002160100024",
        "52010267002160030152",
        "52010367002160000036",
        "52010367002160000138",
        "52010367002160000095",
        "52010367002160000184",
        "52010367002160000057",
        "52010367002160000127",
        "52010167002160000047",
        "52011104012167101000",
        "52010267002160030029",
        "52010267002160100051",
        "52010267002160030001",
        "52010267002160030138",
        "52012267002160000067",
        "52012267002160000031",
        "52012267002160000176",
        "52010267002160020001",
        "52010367002160000261",
        "52010367002160000237",
        "52010367002160000015",
        "52010367002160000081",
        "52012267002160000141",
        "52019967002160000001",
        "52010267002160030127",
        "52010267002160030142",
        "52010267002160030032",
        "52010267002160030091",
        "52012267002160000063",
        "52010367002160000011",
        "52010367002160000252",
        "52010367002160000085",
        "52012267002160000124",
        "52012267002160000113",
        "52012267002160000172",
        "52010167002160000029",
        "52012267002160000035",
        "52010267002160030101",
        "52010267002160030084",
        "52012267002160000131",
        "52012267002160000020",
        "52010367002160000300",
        "52010367002160000285",
        "52010367002160000065",
        "52010367002160000033",
        "52010367002160000090",
        "52010367002160000052",
        "52010367002160000247",
        "52010167002160000014",
        "52010367002160000115",
        "52010267002160030156",
        "52010267002160030161",
        "52010267002160030047",
        "52010267002160030058",
        "52012267002160000009",
        "52010367002160000114",
        "52010367002160000227",
        "52010367002160000246",
        "52010167002160000022",
        "52010367002160000005",
        "52010267002160030212",
        "52010267002160030079",
        "52010267002160030051",
        "52010267002160100048",
        "52010267002160030007",
        "52010267002160100057",
        "52010267002160030140",
        "52010267002160030030",
        "52012267002160000037",
        "52010267002160020018",
        "52012267002160000056",
        "52010367002160000135",
        "52010367002160000098",
        "52012267002160000111",
        "52010367002160000102",
        "52010167002160000034",
        "52012267002160000139",
        "52012267002160000061",
        "52010367002160000206",
        "52010267002160100035",
        "52010267002160030122",
        "52010267002160100054",
        "52010267002160030143",
        "52012267002160000144",
        "52012267002160000173",
        "52010267002160020004",
        "52010367002160000084",
        "52010367002160000059",
        "52010367002160000010",
        "52010367002160000129",
        "52010167002160000037",
        "52012267002160000055",
        "52012267002160000097",
        "52010367002160000148",
        "52010267002160100040",
        "52010267002160030148",
        "52010267002160030059",
        "52010267002160030157",
        "52010267002160030046",
        "52010267002160100021",
        "52010267002160020010",
        "52012267002160000017",
        "52010367002160000143",
        "52010167002160000042",
        "52012267002160000119",
        "52010367002160000270",
        "52012267002160000178",
        "52011104012167102101",
        "52010267002160030023",
        "52010267002160000105",
        "52010267002160030153",
        "52010267002160030042",
        "52010267002160030014",
        "52012267002160000154",
        "52010367002160000094",
        "52010367002160000222",
        "52010367002160000028",
        "52012267002160000135",
        "52010167002160000046",
        "52010167002160000027",
        "52010267002160030100",
        "52010267002160030085",
        "52010267002160030011",
        "52010267002160030026",
        "52010267002160100041",
        "52012267002160000016",
        "52010267002160100017",
        "52010367002160000091",
        "52010367002160000064",
        "52010367002160000123",
        "52012267002160000040",
        "52010367002160000284",
        "52010167002160000043",
        "52010367002160000032",
        "52012267002160000151",
        "52012267002160000166",
        "52011267002160000001",
        "52010067002160000001",
        "52010267002160100029",
        "52010267002160030066",
        "52010267002160030093",
        "52010267002160030116",
        "52010267002160100036",
        "52010267002160030018",
        "52010267002160020007",
        "52012267002160000049",
        "52012267002160000158",
        "52010367002160000308",
        "52010367002160000267",
        "52010367002160000163",
        "52010367002160000024",
        "52012267002160000126",
        "52010167002160000003",
        "52012267002160000147",
        "52010267002160030181",
        "52010267002160030004",
        "52012267002160000062",
        "52010367002160000071",
        "52010367002160000027",
        "52010367002160000160",
        "52010367002160000205",
        "52010367002160000038",
        "52012267002160000125",
        "52012267002160000112",
        "52010167002160000049",
        "52010367002160000101",
        "52012267002160000088",
        "52010267002160030118",
        "52010267002160100027",
        "52010267002160000151",
        "52010267002160030166",
        "52010267002160030040",
        "52010267002160020016",
        "52010367002160000269",
        "52010367002160000217",
        "52010367002160000089",
        "52012267002160000161",
        "52010167002160000044",
        "52010367002160000241",
        "52010367002160000172",
        "52010267002160030171",
        "52010267002160100030",
        "52010267002160030036",
        "52010267002160030048",
        "52010267002160030159",
        "52012267002160000019",
        "52012267002160000006",
        "52012267002160000050",
        "52010367002160000256",
        "52010367002160000228",
        "52010367002160000133",
        "52010367002160000249",
        "52010367002160000022",
        "52012267002160000117",
        "52010167002160000005",
        "52010167002160000032",
        "52010267002160010001",
        "52010267002160030125",
        "52010267002160030055",
        "52010267002160030173",
        "52010267002160030062",
        "52010267002160030097",
        "52012267002160000065",
        "52010367002160000069",
        "52010367002160000263",
        "52010367002160000167",
        "52010367002160000119",
        "52010167002160000030",
        "52010367002160000178",
        "52012267002160000174",
        "52012267002160000004",
        "52010167002160000007",
        "52010367002160000131",
        "52010167002160000051",
        "52010267002160100038",
        "52010267002160030151",
        "52010267002160030068",
        "52010267002160030009",
        "52010267002160030077",
        "52010267002160100046",
        "52010267002160030130",
        "52012267002160000011",
        "52010367002160000145",
        "52010367002160000113",
        "52010367002160000276",
        "52012267002160000137",
        "52012267002160000149",
        "52012267002160000085",
        "52010367002160000283",
        "52012267002160000128",
        "52010367002160000306",
        "52010167002160000012",
        "52010267002160030121",
        "52010267002160030109",
        "52010267002160030168",
        "52012267002160000170",
        "52010367002160000045",
        "52010367002160000219",
        "52010367002160000278",
        "52010367002160000072",
        "52010367002160000250",
        "52010367002160000013",
        "52010267002160030083",
        "52010267002160100047",
        "52010267002160030167",
        "52010267002160030041",
        "52010267002160100026",
        "52010267002160030008",
        "52012267002160000157",
        "52012267002160000027",
        "52010367002160000144",
        "52012267002160000129",
        "52010367002160000003",
        "52010267002160100058",
        "52010267002160030124",
        "52010267002160100052",
        "52010267002160030172",
        "52010267002160030145",
        "52010267002160030002",
        "52012267002160000032",
        "52012267002160000053",
        "52010367002160000021",
        "52010367002160000009",
        "52012267002160000114",
        "52012267002160000091",
        "52010367002160000082",
        "52010367002160000203",
        "52010167002160000006",
        "52010167002160000019",
        "52010167002160000031",
        "52010367002160000179",
        "52019967002160000002",
        "root00000000",
        "52010267002160030147",
        "52010267002160030126",
        "52010267002160030049",
        "52010267002160030139",
        "52010267002160030094",
        "52010267002160100019",
        "52010267002160030111",
        "52012267002160000030",
        "52012267002160000018",
        "52010367002160000042",
        "52010367002160000248",
        "52010367002160000132",
        "52010367002160000257",
        "52010367002160000201",
        "52012267002160000177",
        "52010367002160000014",
        "52010367002160000075",
        "52010167002160000004",
        "52018167002160000001",
        "52010267002160100049",
        "52010267002160030108",
        "52010267002160030067",
        "52010267002160030078",
        "52010267002160020006",
        "52012267002160000146",
        "52010367002160000103",
        "52010367002160000197",
        "52010367002160000134",
        "52012267002160000159",
        "52010167002160000035",
        "52010367002160000188",
        "52012267002160000171",
        "52010167002160000002",
        "52010367002160000025",
        "52010367002160000099",
        "52010267002160030017",
        "52010267002160100039",
        "52010267002160030069",
        "52010267002160020008",
        "52012267002160000059",
        "52012267002160000136",
        "52012267002160000160",
        "52012267002160000010",
        "52010367002160000307",
        "52010367002160000199",
        "52010367002160000186",
        "52010367002160000282",
        "52010367002160000221",
        "52010367002160000277",
        "52012267002160000071",
        "52010367002160000112",
        "52010367002160000062",
        "52010167002160000045",
        "52010367002160000173",
        "52010367002160000034",
        "52010267002160030163",
        "52010267002160030102",
        "52010267002160030045",
        "52010267002160030013",
        "52010267002160030154",
        "52010267002160030072",
        "52010267002160020013",
        "52012267002160000023",
        "52010367002160000182",
        "52010367002160000303",
        "52010367002160000121",
        "52010367002160000140",
        "52010367002160000109",
        "52010367002160000030",
        "52012267002160000105",
        "52010167002160000041",
        "52010167002160000017",
        "52010267002160030087",
        "52010267002160030135",
        "52010367002160000051",
        "52010367002160000066",
        "52010367002160000007",
        "52010167002160000008",
        "52010167002160000020",
        "52010367002160000212",
        "52000000002161100001",
        "52010267002160030128",
        "52010267002160100020",
        "52010267002160030070",
        "52010267002160030137",
        "52010267002160030149",
        "52010267002160030039",
        "52012267002160000130",
        "52012267002160000021",
        "52010267002160020011",
        "52012267002160000179",
        "52010367002160000142",
        "52010367002160000180",
        "52010367002160000259",
        "52010367002160000175",
        "52010367002160000053",
        "52010367002160000271",
        "52012267002160000118",
        "52012267002160000107",
        "52010167002160000015",
        "52010367002160000301",
        "52010267002160100032",
        "52010267002160030112",
        "52010267002160100053",
        "52010267002160000277",
        "52010267002160030003",
        "52012267002160000052",
        "52010367002160000289",
        "52010367002160000041",
        "52010367002160000106",
        "52010367002160000083",
        "52010367002160000202",
        "52012267002160000143",
        "52010367002160000192",
        "52012267002160000122",
        "52019967002160000003",
        "52010267002160030162",
        "52010267002160100042",
        "52010267002160030134",
        "52012267002160000152",
        "52012267002160000015",
        "52010367002160000019",
        "52010367002160000141",
        "52010167002160000040",
        "52010367002160000108",
        "52010367002160000120",
        "52012267002160000133",
        "52012267002160000104",
        "52012367002160000001",
        "52011567002160000001",
        "52010267002160030150",
        "52010267002160030106",
        "52010267002160030119",
        "52010267002160030131",
        "52010267002160030076",
        "52010267002160020017",
        "52012267002160000038",
        "52012267002160000148",
        "52010367002160000209",
        "52010367002160000268",
        "52010367002160000216",
        "52010367002160000097",
        "52010367002160000125",
        "52012267002160000101",
        "52012267002160000046",
        "52010167002160000024",
        "52010367002160000055",
        "52010167002160000013",
        "52012267002160000084",
        "52010267002160100034",
        "52010267002160030005",
        "52010267002160030114",
        "52010267002160030123",
        "52012267002160000054",
        "52010367002160000161",
        "52010367002160000047",
        "52010367002160000070",
        "52010367002160000100",
        "52010367002160000204",
        "52010367002160000149",
        "52010367002160000128",
        "52010367002160000137",
        "52010367002160000026",
        "52010167002160000048",
        "52011367002160000001",
        "52012267002160000002",
        "52010267002160100033",
        "52010267002160030035",
        "52012267002160000175",
        "52010367002160000234",
        "52010367002160000118",
        "52010367002160000130",
        "52010367002160000288",
        "52012267002160000123",
        "52010367002160000166",
        "52010367002160000193",
        "52012267002160000005",
        "52010267002160030027",
        "52010267002160030160",
        "52010267002160030136",
        "52010267002160030010",
        "52010267002160030071",
        "52010267002160030129",
        "52010267002160030038",
        "52012267002160000150",
        "52012267002160000167",
        "52012267002160000041",
        "52012267002160000008",
        "52010367002160000122",
        "52010367002160000004",
        "52010367002160000258",
        "52010367002160000211",
        "52010167002160000023",
        "52012267002160000106",
        "52010367002160000226",
        "52010267002160030081",
        "52010267002160030015",
        "52010267002160030190",
        "52010267002160030022",
        "52012267002160000025",
        "52010267002160100013",
        "52010267002160020015",
        "52012267002160000134",
        "52010367002160000110",
        "52010367002160000029",
        "52010367002160000305",
        "52010367002160000159",
        "52010367002160000223",
        "52010367002160000242",
        "52010167002160000026",
        "52012267002160000155",
        "52012267002160000162",
        "52012267002160000103",
        "52010367002160000001",
        "52010000002168000001",
        "52010267002160000153",
        "52010267002160030164",
        "52010267002160030075",
        "52010267002160030132",
        "52010267002160100044",
        "52010267002160100025",
        "52012267002160000163",
        "52010367002160000274",
        "52010367002160000304",
        "52010367002160000037",
        "52010367002160000243",
        "52010367002160000281",
        "52010367002160000139",
        "52010167002160000038",
        "52010367002160000170",
        "52010267002160100031",
        "52010267002160030028",
        "52010267002160030170",
        "52010267002160030037",
        "52012267002160000140",
        "52012267002160000066",
        "52012267002160000007",
        "52010167002160000033",
        "52012267002160000116",
        "52012267002160000051",
        "52012267002160000121",
        "52010267002160100055",
        "52010267002160030064",
        "52010267002160030053",
        "52010267002160020005",
        "52010367002160000265",
        "52010367002160000058",
        "52010367002160000039",
        "52012267002160000145",
        "52010167002160000036",
        "52010167002160000001",
        "52010267002160030065",
        "52010267002160030033",
        "52010267002160030115",
        "52010267002160030090",
        "52010267002160030052",
        "52012267002160000034",
        "52010367002160000046",
        "52010367002160000264",
        "52010367002160000136",
        "52010167002160000028",
        "52010367002160000195",
        "52010267002160100028",
        "52010267002160030141",
        "52010267002160100037",
        "52010267002160030117",
        "52010267002160030120",
        "52012267002160000057",
        "52012267002160000060",
        "52010367002160000309",
        "52010367002160000279",
        "52010367002160000207",
        "52010367002160000155",
        "52010267002160030063",
        "52010267002160030096",
        "52010267002160030089",
        "52010267002160030054",
        "52012267002160000142",
        "52012267002160000064",
        "52010367002160000255",
        "52010367002160000068",
        "52010367002160000107",
        "52010367002160000040",
        "52010367002160000016",
        "52010167002160000050",
        "52010367002160000262",
        "52012267002160000180",
        "52012167002160000001",
        "52010267002160030098",
        "52010267002160100043",
        "52010267002160100022",
        "52012267002160000132",
        "52012267002160000014",
        "52012267002160000042",
        "52010367002160000273",
        "52010367002160000116",
        "52010367002160000225",
        "52010367002160000168",
        "52010367002160000286",
        "52010367002160000079",
        "52010367002160000093",
        "52010367002160000018",
        "52012267002160000164",
        "52012267002160000153",
        # 可以添加更多值
    ]

    all_data = []

    # 为每个regionIndexCode获取数据
    for region_index_code in region_index_codes:
        region_data = fetch_data_for_region(api_config, region_index_code, sync_date)
        all_data.extend(region_data)

    # 保存结果
    save_to_excel(all_data, "camera_data")


if __name__ == "__main__":
    main()
